package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.util.DBUtil;
import com.util.Mode;
import com.model.*;
import com.model.Class;

public class ClassDao {
	Connection conn;
	PreparedStatement ps;
	ResultSet rs;
	
	public int selectByname(String name) {
		conn=DBUtil.getConn();
		int row=-1;
		try {
			ps=conn.prepareStatement("select count(*) from classInfo where isDelete=0 and ClassName=?");
			ps.setString(1, name);
			rs=ps.executeQuery();
			if(rs.next()) {
				row=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
	
	public int insertAll(String name,String pd) {
		conn=DBUtil.getConn();
		int row=-1;
		String sql="";
		if(!pd.equals("修改")) {
			sql="insert into classInfo values(null,?,1)";
		}else {
			sql="update classInfo set isDelete=1 where ClassName=?";
		}
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, name);
			row=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
	
	public int updateById(Class c) {
		conn=DBUtil.getConn();
		int row=-1;
		try {
			ps=conn.prepareStatement("update classInfo set ClassName=? where ClassId=?");
			ps.setString(1, c.getClassName());
			ps.setInt(2, c.getClassId());
			row=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
	
	public int deleteById(int id) {
		conn=DBUtil.getConn();
		int row=-1;
		try {
			ps=conn.prepareStatement("update classInfo set isdelete=0 where ClassId=?");
			ps.setInt(1, id);
			row=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
	
	public int seelctCount(String name){
		int count=0;
		conn=DBUtil.getConn();
		String sql="select count(*) from classInfo where isdelete=1";
		if(name!=null&&!"".equals(name)) {
			sql="select count(*) from classInfo where isdelete=1 and className like'%"+name+"%'";
		}
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()) {
				count=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return count;
	}
	
	public Mode selectAll(int page,int rows,String name) {
		Mode m=new Mode();
		ArrayList<Class> list=new ArrayList<Class>();
		conn=DBUtil.getConn();
		page=(page-1)*rows;
		String sql="select * from classInfo where isdelete=1 limit ?,?";
		if(name!=null&&!"".equals(name)) {
			sql="select * from classInfo where isdelete=1 and ClassName like'%"+name+"%' limit ?,?";
		}
		try {
			ps=conn.prepareStatement(sql);
			
			ps.setInt(1, page);
			ps.setInt(2, rows);
			rs=ps.executeQuery();
			while(rs.next()) {
				Class c=new Class();
				c.setClassId(rs.getInt(1));
				c.setClassName(rs.getString(2));
				list.add(c);
			}
			m.setRows(list);
			m.setTotal(seelctCount(name));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return m;
	}
	
	public ArrayList<Class> selectAll() {
		ArrayList<Class> list=new ArrayList<Class>();
		conn=DBUtil.getConn();
		try {
			ps=conn.prepareStatement("select * from classInfo where isdelete=1");
			rs=ps.executeQuery();
			while(rs.next()) {
				Class c=new Class();
				c.setClassId(rs.getInt(1));
				c.setClassName(rs.getString(2));
				list.add(c);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return list;
	}
	
	public int selectIdByName(String name) {
		conn=DBUtil.getConn();
		int id=0;
		try {
			ps=conn.prepareStatement("select ClassId from classInfo where ClassName=?");
			ps.setString(1, name);
			rs=ps.executeQuery();
			if(rs.next()) {
				id=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return id;
	}
	
	public ArrayList<Class> selectFbBjByAll(int sjid) {
		ArrayList<Class> list=new ArrayList<Class>();
		conn=DBUtil.getConn();
		try {
			ps=conn.prepareStatement("select c.* from classInfo c where c.classid not in(select distinct c.classid from classInfo c,curriInfo ci,paperInfo pa,publishExamPlan pe where pe.paperid=pa.paperid and pe.classid=c.classid and pa.curid=ci.curid and pe.paperid=?) and isdelete=1");
			ps.setInt(1, sjid);
			rs=ps.executeQuery();
			while(rs.next()) {
				Class c=new Class();
				c.setClassId(rs.getInt(1));
				c.setClassName(rs.getString(2));
				list.add(c);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return list;
	}
}
